unit DBEngine;
//YXC_2010_10_19_16_32_21
//YXC_2011_06_14_10_31_40_>Add DoSaveDB.
//must be override the follow metheds
{
  protected
    procedure SetParameters;override;
    function  GetStrInsert:string;override;
    function  GetStrUpdate:string;override;
    function  GetStrDelete:string;override;
  public
    function  GetNextIdex:Integer;overload;
    function  GetNextIdex(AADOCon:TADOConnection):Integer;overload;
  public
    function  CheckExist(AADOCon:TADOConnection):Boolean;override;       
  public
    class function ReadDS(AADODS:TADODataSet):TDBEngine;override;
  public
    class function ReadFromXML(ANode: IXMLNode): TDBEngine;override;
    function WriteToXML(ANode: IXMLNode): IXMLNode;override;    
}

{
    //YXC_2010_10_22_10_09_32_
    procedure Inherit(ABill:TBill);
    function  ClonObj(AObj:TObject):TObject
}

interface
uses
  Classes,SysUtils,ADODB,XMLDOC, XMLIntf,Class_DBPool;

type  
  TDBEngine = class;
  TDBEngineClass =class of TDBEngine;
  TOperateType   =(otNone, otAdd, otEdit, otDel);

  TXMLEngine=class(TObject)
  public
    class function StrsFromXML(ANode:IXMLNode):TStringList;virtual;
    class function ReadFromXML(ANode: IXMLNode): TDBEngine;virtual;//abstract;
    function WriteToXML(ANode: IXMLNode): IXMLNode;virtual;

    class function LoadFromStream(AStream: TStream): TDBEngine;virtual;
    procedure SaveToStream(AStream: TStream);virtual;

    class function LoadFromFile(AFileName: string): TDBEngine;virtual;
    procedure SaveToFile(AFileName: string);virtual;
  end;

  TDBEngine=class(TXMLEngine)
  public
    FStrSQL:string;
    FADOCmd:TADOCommand;
    FOpType:TOperateType;
  protected
    function  GetADOCommand:TADOCommand;
  protected
    procedure SetParameters;virtual;abstract;
    function  GetStrInsert:string;virtual;abstract;
    function  GetStrUpdate:string;virtual;abstract;
    function  GetStrDelete:string;virtual;abstract;
    function  GetStrDeltFL:string;virtual;abstract;
  public
    procedure InsertDB(AADOCon:TADOConnection);overload;virtual;
    procedure UpdateDB(AADOCon:TADOConnection);overload;virtual;
    procedure DeleteDB(AADOCon:TADOConnection);overload;virtual;
    procedure DeleteFL(AADOCon:TADOConnection);overload;virtual;
    procedure DoSaveDB(AADOCon:TADOConnection);overload;virtual;

    procedure InsertDB;overload;virtual;
    procedure UpdateDB;overload;virtual;
    procedure DeleteDB;overload;virtual;
    procedure DeleteFL;overload;virtual;
    procedure DoSaveDB;overload;virtual;
    //YXC_2010_10_31_18_12_31
    function  CheckExist(AADOCon:TADOConnection):Boolean;overload;virtual;
  public
    class function  ReadDS(ADODS: TADODataSet): TDBEngine;virtual;abstract;

    class function  StrsDB(ASQL:string;AADOCon:TADOConnection):TStringList;overload;virtual;
    class function  StrsDB(ASQL:string):TStringList;overload;virtual;

    class function  ReadDB(ASQL:string;AADOCon:TADOConnection):TDBEngine;overload;virtual;
    class function  ReadDB(ASQL:string):TDBEngine;overload;virtual;

    class function  StrsDB(ASQL:string;Fields:array of string;AADOCon:TADOConnection):TStringList;overload;virtual;
    class function  StrsDB(ASQL:string;Fields:array of string):TStringList;overload;virtual;

    class function  GetDataSet(ASQL:string;AADOCon:TADOConnection):TADODataSet;overload;
    class function  GetDataSet(ASQL:string):TADODataSet;overload;

    class function  CheckExist(ATable:string;Args:array of Variant):Boolean;overload;
    class function  CheckField(AField,ATable:string;Args:array of Variant):Integer;

    class procedure ExecuteSQL(ASQL:string;AADOCon:TADOConnection);overload;
    class procedure ExecuteSQL(ASQL:string);overload;    
  public
    property  ADOCmd:TADOCommand read GetADOCommand;
  end;

implementation

{uses
  UtilLib_Global;}



{ TXMLEngine }

class function TXMLEngine.LoadFromFile(AFileName: string): TDBEngine;
begin

end;

class function TXMLEngine.LoadFromStream(AStream: TStream): TDBEngine;
begin

end;


class function TXMLEngine.ReadFromXML(ANode: IXMLNode): TDBEngine;
begin

end;

procedure TXMLEngine.SaveToFile(AFileName: string);
begin

end;

procedure TXMLEngine.SaveToStream(AStream: TStream);
begin

end;

class function TXMLEngine.StrsFromXML(ANode: IXMLNode): TStringList;
var
  I:Integer;
  ObjA:TDBEngine;
  NodA:IXMLNode;
begin
  Result:=nil;
  if ANode=nil then Exit;
  if ANode.ChildNodes.Count=0 then Exit;
  Result:=TStringList.Create;
  NodA:=ANode.ChildNodes.First;
  while NodA<>nil do
  begin
    ObjA:=ReadFromXML(NodA);
    Result.AddObject('',ObjA);
    NodA:=NodA.NextSibling;
  end;
end;

function TXMLEngine.WriteToXML(ANode: IXMLNode): IXMLNode;
begin

end;

{ TDBEngine }


procedure TDBEngine.DeleteDB(AADOCon: TADOConnection);
begin
  try
    FStrSQL:=GetStrDelete;
    if FStrSQL='' then raise Exception.Create('you must be override getstrdelete method');
    FOpType:=otDel;
    ADOCmd.Connection:=AADOCon;
    FADOCmd.Prepared :=True;
    FADOCmd.CommandText:=FStrSQL;
    SetParameters;
    try
      FADOCmd.Execute;
    except
      On E:Exception do
      begin
        raise Exception.Create(E.Message);
      end;
    end;
  finally
    FreeAndNil(FADOCmd);
  end;
end;

procedure TDBEngine.DeleteFL(AADOCon: TADOConnection);
begin
  try
    FStrSQL:=GetStrDeltFL;
    if FStrSQL='' then raise Exception.Create('you must be override getstrdeltfl method');
    FOpType:=otDel;
    ADOCmd.Connection:=AADOCon;
    FADOCmd.Prepared :=True;
    FADOCmd.CommandText:=FStrSQL;
    SetParameters;
    try
      FADOCmd.Execute;
    except
      On E:Exception do
      begin
        raise Exception.Create(E.Message);
      end;
    end;
  finally
    FreeAndNil(FADOCmd);
  end;
end;


procedure TDBEngine.DeleteFL;
var
  ADOCon:TADOConnection;
begin
  try
    ADOCon:=TDBPool.GetConnect();
    DeleteFL(ADOCon);
  finally
    FreeAndNil(ADOCon);
  end;
end;

function TDBEngine.GetADOCommand: TADOCommand;
begin
  if FADOCmd=nil then
  begin
    FADOCmd := TADOCommand.Create(nil);
  end;
  Result:= FADOCmd;
end;

procedure TDBEngine.InsertDB(AADOCon: TADOConnection);
begin
  try
    FStrSQL:=GetStrInsert;
    if FStrSQL='' then raise Exception.Create('you must be override getstrinsert method');
    FOpType:=otAdd;
    ADOCmd.Connection:=AADOCon;
    FADOCmd.Prepared :=True;
    FADOCmd.CommandText:=FStrSQL;
    SetParameters;
    try
      FADOCmd.Execute;
    except
      On E:Exception do
      begin
        raise Exception.Create(E.Message);
      end;  
    end;
  finally
    FreeAndNil(FADOCmd);
  end;
end;

procedure TDBEngine.InsertDB;
var
  ADOCon:TADOConnection;
begin
  try
    ADOCon:=TDBPool.GetConnect();
    InsertDB(ADOCon);
  finally
    FreeAndNil(ADOCon);
  end;
end;

class function TDBEngine.StrsDB(ASQL: string;
  AADOCon: TADOConnection): TStringList;
var
  ADODS:TADODataSet;
  AObj :TDBEngine;
begin
  Result:=nil;
  try
    ADODS:=TADODataSet.Create(nil);
    ADODS.Prepared   :=True;
    ADODS.Connection :=AADOCon;
    if ASQL='' then raise Exception.Create('SQL=NIL');
    ADODS.CommandText:=ASQL;
    ADODS.Open;
    if ADODS.RecordCount=0 then Exit;
    Result:=TStringList.Create;
    while not ADODS.Eof do
    begin
      AObj:=ReadDS(ADODS);
      Result.AddObject('',AObj);
      ADODS.Next;
    end;      
  finally
    FreeAndNil(ADODS);
  end;
end;

procedure TDBEngine.UpdateDB(AADOCon: TADOConnection);
begin
  try
    FStrSQL:=GetStrUpdate;
    if FStrSQL='' then raise Exception.Create('you must be override getstrupdate method');
    FOpType:=otEdit;
    ADOCmd.Connection:=AADOCon;
    FADOCmd.Prepared :=True;
    FADOCmd.CommandText:=FStrSQL;
    SetParameters;
    try
      FADOCmd.Execute;
    except
      On E:Exception do
      begin
        raise Exception.Create(E.Message);
      end;  
    end;
  finally
    FreeAndNil(FADOCmd);
  end;
end;
procedure TDBEngine.DeleteDB;
var
  ADOCon:TADOConnection;
begin
  try
    ADOCon:=TDBPool.GetConnect();
    DeleteDB(ADOCon);
  finally
    FreeAndNil(ADOCon);
  end;
end;

procedure TDBEngine.UpdateDB;
var
  ADOCon:TADOConnection;
begin
  try
    ADOCon:=TDBPool.GetConnect();
    UpdateDB(ADOCon);
  finally
    FreeAndNil(ADOCon);
  end;
end;


class function TDBEngine.StrsDB(ASQL: string): TStringList;
var
  ADOCon:TADOConnection;
begin
  try
    ADOCon:=TDBPool.GetConnect();
    Result:=StrsDB(ASQL,ADOCon);
  finally
   FreeAndNil(ADOCon);
  end;
end;

class function TDBEngine.StrsDB(ASQL: string; Fields: array of string;
  AADOCon: TADOConnection): TStringList;
var
  ADODS:TADODataSet;
  AObj :TDBEngine;
  //YXC_2010_08_09_11_30_01
  I:Integer;
  TempA:string;
  TempB:string;
begin
  Result:=nil;
  try
    ADODS:=TADODataSet.Create(nil);
    ADODS.Prepared   :=True;
    ADODS.Connection :=AADOCon;
    if ASQL='' then raise Exception.Create('SQL=NIL');
    ADODS.CommandText:=ASQL;
    ADODS.Open;
    if ADODS.RecordCount=0 then Exit;
    Result:=TStringList.Create;
    while not ADODS.Eof do
    begin
      AObj:=ReadDS(ADODS);

      TempA:='';
      if Length(Fields)<>0 then
      begin
        for I:=0 to Length(Fields)-1 do
        begin
          TempB:=Trim(ADODS.FieldByName(Fields[I]).AsString);
          TempA:=TempA+'-'+TempB;
        end;
        Delete(TempA,1,1);        
      end;

      Result.AddObject(TempA,AObj);
      ADODS.Next;
    end;
  finally
    FreeAndNil(ADODS);
  end;
end;

class function TDBEngine.StrsDB(ASQL: string;
  Fields: array of string): TStringList;
var
  ADOCon:TADOConnection;
begin
  try
    ADOCon:=TDBPool.GetConnect();
    Result:=StrsDB(ASQL,Fields,ADOCon);
  finally
    FreeAndNil(ADOCon);
  end;
end;

class procedure TDBEngine.ExecuteSQL(ASQL: string;
  AADOCon: TADOConnection);
var
  ADOCmd:TADOCommand;
begin
  try
    ADOCmd:=TADOCommand.Create(nil);
    ADOCmd.Connection:=AADOCon;
    ADOCmd.CommandText:=ASQL;
    ADOCmd.Execute;
  finally
    FreeAndNil(ADOCmd);
  end;
end;

class procedure TDBEngine.ExecuteSQL(ASQL: string);
var
  ADOCon:TADOConnection;
begin
  try
    ADOCon:=TDBPool.GetConnect();
    ExecuteSQL(ASQL,ADOCon);
  finally
    FreeAndNil(ADOCon);
  end;
end;

class function TDBEngine.ReadDB(ASQL: string;
  AADOCon: TADOConnection): TDBEngine;
var
  ADODS:TADODataSet;
begin
  Result:=nil;
  try
    ADODS:=TADODataSet.Create(nil);
    ADODS.Prepared   :=True;
    ADODS.Connection :=AADOCon;
    if ASQL='' then raise Exception.Create('SQL=NIL');
    ADODS.CommandText:=ASQL;
    ADODS.Open;
    if ADODS.RecordCount=0 then Exit;
    while not ADODS.Eof do
    begin
      Result:=ReadDS(ADODS);
      ADODS.Next;
    end;      
  finally
    FreeAndNil(ADODS);
  end;
end;

class function TDBEngine.ReadDB(ASQL: string): TDBEngine;
var
  ADOCon:TADOConnection;
begin
  try
    ADOCon:=TDBPool.GetConnect();
    Result:=ReadDB(ASQL,ADOCon);
  finally
    FreeAndNil(ADOCon);
  end;
end;

class function TDBEngine.CheckExist(ATable: string;
  Args: array of Variant): Boolean;
var
  ADOCon:TADOConnection;
begin
  try
    ADOCon:=TDBPool.GetConnect();
    //Result:=UtilLib_Global.TryCheckExist(ADOCon,ATable,Args);
  finally
    FreeAndNil(ADOCon);
  end;
end;


class function TDBEngine.CheckField(AField, ATable: string;
  Args: array of Variant): Integer;
var
  ADOCon:TADOConnection;
begin
  try
    ADOCon:=TDBPool.GetConnect();
    //Result:=UtilLib_Global.TryCheckField(ADOCon,AField,ATable,Args);
  finally
    FreeAndNil(ADOCon);
  end;
end;

function TDBEngine.CheckExist(AADOCon: TADOConnection): Boolean;
begin

end;

procedure TDBEngine.DoSaveDB(AADOCon: TADOConnection);
begin
  if CheckExist(AADOCon) then
  begin
    UpdateDB(AADOCon);
  end else
  begin
    InsertDB(AADOCon);
  end;    
end;

procedure TDBEngine.DoSaveDB;
var
  ADOCon:TADOConnection;
begin
  try
    ADOCon:=TDBPool.GetConnect();
    DoSaveDB(ADOCon);
  finally
    FreeAndNil(ADOCon);
  end;
end;

class function TDBEngine.GetDataSet(ASQL: string;
  AADOCon: TADOConnection): TADODataSet;
begin
  Result:=nil;
  if Trim(ASQL)='' then Exit;

  Result:=TADODataSet.Create(nil);
  Result.Connection :=AADOCon;
  Result.CommandText:=ASQL;
  Result.Open;
end;

class function TDBEngine.GetDataSet(ASQL: string): TADODataSet;
var
  ADOCon:TADOConnection;
begin
  try
    ADOCon:=TDBPool.GetConnect();
    Result:=GetDataSet(ASQL,ADOCon);
  finally
    FreeAndNil(ADOCon);
  end;
end;

end.
